//
//  SCStatusDAL.swift
//  GZWeibo007
//
//  Created by newuser on 16/1/3.
//  Copyright © 2016年 Apple. All rights reserved.
//

import UIKit

///加载数据 1.本地数据 2.从网络加载
class SCStatusDAL: NSObject {
    
    //当程序到后台,清除数据库的微博数据
    private let overTime: NSTimeInterval = 60 
    
    

    //单列
    static let sharedInstance = SCStatusDAL()
    
    private override init() {
        super.init()
    }
    
    
    ///清除数据库的过时微博数据
    func clearCachedStatus() {
       //计算过期时间
        let overDate = NSDate(timeIntervalSinceNow: -overTime)
        //数据库保存格式 2016-01-03 16:20:56
        print("过期时间:\(overDate)")
        
        //定义dateForatter
        let df = NSDateFormatter()
        
        //设置日期转换格式
        df.dateFormat = "yyyy-MM-dd HH:mm:ss"
        
        let dateString = df.stringFromDate(overDate)
        print("dateString\(dateString)")
        
        //编写sql语句删除过期数据
        let sql = "DELETE FROM T_Status WHERE createTime < '\(dateString)';"
        
        SCSQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if db.executeUpdate(sql) {
                print("清除数据成功")
            } else {
              print("清除数据失败")
            }
        }
        
    }
    
   ///加载数据 1.本地数据 2.从网络加载
    func loadStatus(since_id: Int, max_id: Int, finished: (statuses: [[String: AnyObject]]?, error: NSError?) -> ()) {
    
        //1.从本地库加载
        loadCachedStatus(since_id, max_id: max_id) { (stattuses) -> () in
            if stattuses != nil && stattuses!.count > 0 {
                //本地有数据
                finished(statuses: stattuses, error: nil)
                return
            }
            //没有数据
            CZNetworkTool.sharedInstance.loadStatus(since_id, max_id: max_id, finished: { (result, error) -> () in
                if error != nil {
                   //网络加载数据失败
                    finished(statuses: nil, error: error)
                    return
                }
                
                //3.如果本地库没有,在从网络加载
                //4,网络加载后保存到数据库
                //将加载网络返回数据返回给调用者
                //从网络加载数据后保存到数据库
                //判断字典是否有statuses将statuses的值转成数组,数组里面存放的是字典
                if let array = result?["statuses"] as? [[String: AnyObject]] {
                   self.saveStatus(array)
                   finished(statuses: array, error: nil)
                } else {
                
                    finished(statuses: nil, error: nil)
                }
            })
        }  
    }
    
    /**
     1. 从本地数据库加载
     
     - parameter since_id: 下拉刷新使用
     - parameter max_id:   上拉加载更多使用
     */
   private func loadCachedStatus(since_id: Int, max_id: Int, finished:(stattuses: [[String: AnyObject]]?) -> ()) {
        //获取用户id
        let userId = CZUserAccount.loadUserAccount()?.uid
        assert(userId != nil, "用户id为空")
        
        //编写sql语句
        var sql =  "SELECT statusId, status, userId FROM T_Status \n" +
        "WHERE userId = \(userId!) \n"
        
        //下拉刷新
        if since_id > 0 {
              sql += "AND statusId > \(since_id) \n"
        } else if max_id > 0 {
             sql += "AND statusId < \(max_id) \n"
        }
        //拼接根据微博id排序
        sql += "ORDER BY statusId DESC LIMIT 20;"
        
        print("查询微博数据的sql: \(sql)")
        
        SCSQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if let recordSet = db.executeQuery(sql) {
                //获取的微博数据
                var statusesArray = [[String: AnyObject]]()
                while recordSet.next() {
                   //微博数据的字符串
                    let string = recordSet.stringForColumn("status")
                    //字符串转字典
                    let data = string.dataUsingEncoding(NSUTF8StringEncoding)!
                    //微博字典
                    let dict = try! NSJSONSerialization.JSONObjectWithData(data, options: NSJSONReadingOptions(rawValue: 0)) as! [String: AnyObject]
                    statusesArray.append(dict)
                }
                
                //获取到了数据
                finished(stattuses: statusesArray)
            } else {
            
                print("没有获取到数据")
                finished(stattuses: nil)
                return
            }
        }
    }
    
    
    /**
    4. 将微博数据保存到数据库
    
    - parameter statuses: 要保存的微博数据数组
    */
   private func saveStatus(statuses: [[String: AnyObject]]) {
      let sql = "INSERT OR REPLACE INTO \(T_Status) (statusId, status, userId) VALUES (?, ?, ?);"
       print("添加微博数据sql:\(sql)")
        
        //获取用户的id
        let userId = CZUserAccount.loadUserAccount()?.uid
        assert(userId != nil, "用户id为空")
        SCSQLiteManager.sharedManager.dbQueue.inTransaction { (db, rollback) -> Void in
            //遍历数组,获取数组里面的字典.保存到数据库
            for dict in statuses {
            
                //获取微博的id
                let id = dict["id"] as! Int
                
                // sqlite不支持将字典放入数据库,将微博字典转成字符串
                // 字典转字符串 字典 转成 NSData 转成 字符串
                // try! 强制try
                let data = try! NSJSONSerialization.dataWithJSONObject(dict, options: NSJSONWritingOptions.PrettyPrinted)
                let string = String(data: data, encoding: NSUTF8StringEncoding)!
                
                //如果添加数据有失败的
                if !db.executeUpdate(sql, id, string, userId!) {
                    print("添加数据出错误,回滚事物")
                    rollback.memory = true
                    break
                }
            }
            print("添加 \(statuses.count) 条数据到数据库")
      }
   }
}